﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Media.Imaging;
using System.IO;

namespace TinyCinema.Data
{
    public class Movie:Execution
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public Image Poster { get; set; }
        public string Director { get; set; }
        public int Duration { get; set; }
        public int ReleaseYear { get; set; }
        public DateTime ReleaseDate { get; set; }
        public string Description { get; set; }
        public ObservableCollection<Actor> Actors { get; set; }
        public ObservableCollection<Genre> Genres { get; set; }
        public Country Country { get; set; }
        public ObservableCollection<Schedule> Schedules { get; set; }
        public static ObservableCollection<Movie> lstMovie { get; set; }
        public Movie() {
            Poster = new Image();
            Actors = new ObservableCollection<Actor>();            
            Genres = new ObservableCollection<Genre>();
            Genres.Add(new Genre());
            Schedules = new ObservableCollection<Schedule>();
            Country = new Country();
            if (lstMovie == null)
                lstMovie = new ObservableCollection<Movie>();
        }

        public Movie(string _ID)
        {
            if (_ID == null) return;
            Poster = new Image();
            ID = _ID;
            PutTableIntoDataSet(Tables.Movie);
            var tbl = ds.Tables[Tables.Movie];
            var Row = tbl.Select(MovieTable.ID + "='" + ID + "'")[0];
            Name = Row[MovieTable.Name] as String;
            try { Poster = new Image(Row[MovieTable.Poster] as String); }
            catch { }
            Director = Row[MovieTable.Director] as String;
            Description = Row[MovieTable.Description] as String;
            Duration = (int)Row[MovieTable.Duration];
            ReleaseYear = (int)Row[MovieTable.ReleaseYear];
            Country = new Country(Row[MovieTable.CountryID] as String);
            GetActors();
            GetGenres();
            if(lstMovie==null)
            {
                lstMovie = new ObservableCollection<Movie>();
                lstMovie = GetAllMovie();
            }
        }    
        public void GetActors()
        {
            Actors = new ObservableCollection<Actor>();
            PutTableIntoDataSet(Tables.Acting);
            var tbl = ds.Tables[Tables.Acting];
            var Rows = tbl.Select(ActingTable.MovieID + "='" + ID + "'");
            foreach (var row in Rows)
                Actors.Add(new Actor(row[ActingTable.ActorID] as String));
        }
        public void GetGenres()
        {
            Genres = new ObservableCollection<Genre>();
            PutTableIntoDataSet(Tables.OfGenre);
            var tbl = ds.Tables[Tables.OfGenre];
            var Rows = tbl.Select(OfGenreTable.MovieID + "='" + ID + "'");
            foreach (var Row in Rows)
                Genres.Add(new Genre(Row[OfGenreTable.MovieTypeID] as String));
        }
        public void GetSchedules()
        {
            Schedules = new ObservableCollection<Schedule>();
            PutTableIntoDataSet(Tables.Schedule);
            var tbl = ds.Tables[Tables.Schedule];
            var Rows = tbl.Select(ScheduleTable.MovieID + "='" + ID + "'");
            foreach (var Row in Rows)
                Schedules.Add(new Schedule(Row[ScheduleTable.ID] as String));
        }
        public static ObservableCollection<Movie> GetAllMovie()
        {
            PutTableIntoDataSet(Tables.Movie);
            var tbl = ds.Tables[Tables.Movie];
            var Rows = tbl.Select();
            return RowsToMovies(Rows);
        }
        public static ObservableCollection<Movie> SearchByName(string str)
        {
            PutTableIntoDataSet(Tables.Movie);
            var tbl = ds.Tables[Tables.Movie];
            var Rows = tbl.Select(MovieTable.Name + " Like '%" + str + "%'");
            return RowsToMovies(Rows);
        }
        public static ObservableCollection<Movie> SearchByDirector(string str)
        {
            PutTableIntoDataSet(Tables.Movie);
            var tbl = ds.Tables[Tables.Movie];
            var x = MovieTable.Director + " Like %" + str + "%";
            var Rows = tbl.Select(MovieTable.Director + " Like '%" + str + "%'");
            return RowsToMovies(Rows);
        }
        public static ObservableCollection<Movie> SearchByActor(string str)
        {
            string sql = "Select " + MovieTable.ID + " from " + Tables.Acting + "," + Tables.Actor
                + " where " + Tables.Acting + "." + ActingTable.ActorID + "=" + Tables.Actor + "." + ActorTable.ID
                + " and " + Tables.Actor + "." + ActorTable.Name + " Like '%" + str + "%'";

            SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlConnection);
            try { sqlConnection.Open(); }
            catch { }
            adapter.Fill(ds, "Temp");
            sqlConnection.Close();

            var Result = new ObservableCollection<Movie>();
            var tbl = ds.Tables["Temp"];
            var Rows = tbl.Select();
            return RowsToMovies(Rows);
        }
        public static ObservableCollection<Movie> SearchByGenre(string str)
        {
            string sql = "Select " + MovieTable.ID + " from " + Tables.OfGenre + "," + Tables.Genre
                + " where " + Tables.OfGenre + "." + OfGenreTable.MovieTypeID + "=" + Tables.Genre + "." + GenreTable.ID
                + " and " + Tables.Genre + "." + GenreTable.Name + " Like '%" + str + "%'";
            
            SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlConnection);
            try { sqlConnection.Open(); }
            catch { }
            adapter.Fill(ds, "Temp");
            sqlConnection.Close();

            var Result = new ObservableCollection<Movie>();
            var tbl = ds.Tables["Temp"];
            var Rows = tbl.Select();
            return RowsToMovies(Rows);
        }

        private static ObservableCollection<Movie> RowsToMovies(System.Data.DataRow[] Rows)
        {
            var Result = new ObservableCollection<Movie>();
            foreach (var Row in Rows)
                Result.Add(new Movie(Row[MovieTable.ID] as String));
            return Result;
        }

        public void Save()
        {
            #region Lưu phim
            using (var tbl = ds.Tables[Tables.Movie])
            {
                if (ID == null)
                {
                    ID = IDGenerate(Tables.Movie);
                    var Row = tbl.NewRow();
                    Row[MovieTable.ID] = ID;
                    Row[MovieTable.Name] = Name;
                    Row[MovieTable.Poster] = Poster.ToBase64String();
                    Row[MovieTable.Director] = Director;
                    Row[MovieTable.Duration] = Duration;
                    Row[MovieTable.ReleaseYear] = ReleaseYear;
                    Row[MovieTable.Description] = Description;
                    Row[MovieTable.CountryID] = Country.ID;
                    tbl.Rows.Add(Row);
                    lstMovie.Add(this);
                }
                else
                {
                    foreach (System.Data.DataRow Row in tbl.Rows)
                        if ((Row[MovieTable.ID] as String) == ID)
                        {
                            Row[MovieTable.Name] = Name;
                            Row[MovieTable.Poster] = Poster.ToBase64String();
                            Row[MovieTable.Director] = Director;
                            Row[MovieTable.Duration] = Duration;
                            Row[MovieTable.ReleaseYear] = ReleaseYear;
                            Row[MovieTable.Description] = Description;
                            Row[MovieTable.CountryID] = Country.ID;
                            break;
                        }
                }
                SaveCommand(Tables.Movie);
            }
#endregion

            #region Lưu diễn viên - diễn xuất
            PutTableIntoDataSet(Tables.Acting);
            using (var tbl = ds.Tables[Tables.Acting]) 
            {
                //Xóa diễn xuất
                string Condition = ActingTable.MovieID + "='" + ID + "'";
                foreach (var Row in tbl.Select(Condition))
                    Row.Delete();

                //Thêm diễn viên - thêm diễn xuất
                foreach (var Actor in Actors)
                {
                    Actor.Save();
                    var NewRow = tbl.NewRow();
                    NewRow[ActingTable.ActorID] = Actor.ID;
                    NewRow[ActingTable.MovieID] = ID;
                    tbl.Rows.Add(NewRow);
                }
                SqlDataAdapter adapter = new SqlDataAdapter("Select * From " + Tables.Acting, sqlConnection);
                SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(adapter);
                var DeletedRows = tbl.GetChanges(System.Data.DataRowState.Deleted);
                var AddRows = tbl.GetChanges(System.Data.DataRowState.Added);
                try { sqlConnection.Open(); }
                catch { }
                try { adapter.Update(DeletedRows); }
                catch { }
                try { adapter.Update(AddRows); }
                catch { }
                sqlConnection.Close();
            }

            #endregion

            #region Lưu thể loại
            PutTableIntoDataSet(Tables.OfGenre);
            using (var tbl = ds.Tables[Tables.OfGenre])
            {
                string Condition = OfGenreTable.MovieID + "='" + ID + "'";
                foreach(var Row in tbl.Select(Condition))
                    Row.Delete();
                foreach(var Genre in Genres)
                {
                    var NewRow = tbl.NewRow();
                    NewRow[OfGenreTable.MovieID] = ID;
                    NewRow[OfGenreTable.MovieTypeID] = Genre.ID;
                    tbl.Rows.Add(NewRow);
                }

                var sql = "Select * From " + Tables.OfGenre;
                SqlDataAdapter adapter = new SqlDataAdapter(sql,sqlConnection);
                SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(adapter);
                var DeletedRows = tbl.GetChanges(System.Data.DataRowState.Deleted);
                var AddRows = tbl.GetChanges(System.Data.DataRowState.Added);
                try { sqlConnection.Open(); }
                catch { }
                try { adapter.Update(DeletedRows); }
                catch { }
                try{adapter.Update(AddRows);} catch{}
                sqlConnection.Close();
            }
            #endregion

            ds.AcceptChanges();

        }
        public void AddPoster()
        {
            Microsoft.Win32.OpenFileDialog dialog = new Microsoft.Win32.OpenFileDialog();
            dialog.Filter = "Image|*.jpg";
            if (dialog.ShowDialog().Value)
            {
                MemoryStream memoryStream = new MemoryStream();
                BitmapEncoder encoder = new PngBitmapEncoder();
                encoder.Frames.Add(BitmapFrame.Create(new BitmapImage(new Uri(dialog.FileName))));
                encoder.Save(memoryStream);
                var bitmapImage = new BitmapImage();
                bitmapImage.BeginInit();
                bitmapImage.CacheOption = BitmapCacheOption.OnLoad;
                bitmapImage.StreamSource = memoryStream;
                bitmapImage.EndInit();
                Poster.DisplayImage = bitmapImage;
            }
        }
        public void Delete()
        {
            using (var tbl = ds.Tables[Tables.Acting])
            {
                //Xóa diễn xuất
                string Condition = ActingTable.MovieID + "='" + ID + "'";
                var Rows = tbl.Select(Condition);
                if (Rows.Length != 0)
                {
                    foreach (var Row in Rows) Row.Delete();
                    SaveCommand(Tables.Acting);
                }
                
            }
            using (var tbl = ds.Tables[Tables.OfGenre])
            {
                //Xóa thể loại
                string Condition = OfGenreTable.MovieID + "='" + ID + "'";
                var Rows = tbl.Select(Condition);
                if (Rows.Length != 0)
                {
                    foreach (var Row in Rows) Row.Delete();
                    SaveCommand(Tables.OfGenre);
                }
                
            }
            using (var tbl = ds.Tables[Tables.Movie])
            {
                string Condition = MovieTable.ID + "='" + ID + "'";
                tbl.Select(Condition)[0].Delete();
                SaveCommand(Tables.Movie);
            }
            lstMovie = GetAllMovie();
        }

    }
}
